Normalization

De-Normalization
Denormalization is the process of putting one fact in numerous places(its vice-versa of normalization).Only one valid reason exists for denormalizing a relational design to enhance performance.The sacrifice to performance is that you increase redundancy in database.Database Normalization

The term Normalization is a process by which we can efficiently organize the data in a database. It associates relationship between individual tables according to policy designed both to care for the data and to create the database more flexible by eliminating redundancy and inconsistent dependency.

In other words, Database normalization is a process by which a presented database is tailored to bring its component tables into compliance with a sequence of progressive standard forms. It is an organized way of ensuring that a database construction is appropriate for general purpose querying and also includes the functions of insertion, deletion and updating.

Edgar Frank Codd was the person who introduced the process of database normalization firstly in his paper called A Relational Model of Data for Large Shared Data Banks. The two main objective of database normalization is eliminating redundant data and ensuring data dependencies make sense and make sure that every non-key column in every table is directly reliant on the key and the whole key.

Redundant data or unnecessary data will take more and more space in the database and later, creates the maintenance problem in the database. If data that exists in more than one place must be changed because it wastes disk space and the data must be changed in exactly the same way in all locations of the table.

Data dependencies can make data easier to access because it is easy to find the path of the data. Inconsistent dependencies will create the huge problem to find the data of particular person or thing. For example, if a user look in the employee table for the salary of a particular employee, it may not make sense to look there for the HRA or PF of the employee who calls on that customer. The employee’s HRA of PF is related to, or dependent on, the employee.The following are the form of Database Normalization :
1. First Normal Form (1NF)
2. Second Normal Form(2NF)
3. Third Normal Form(3NF)

First Normal Form (1NF)

The first normal form (1NF) sets the very crucial rule to create the database :

1. There are no repeating or duplicate fields.

2. Each cell contains only a single value.

3. Each record is unique and identified by primary key.

The normalization process involves getting our data to adjust in a progressive normal forms and you cannot achieve the higher level of normalization without satisfying the previous levels. The First Normal Form asking the values in each cell of a table must be atomic. The word atomic describes that there should be no sets of values in one particular cell.

Let’s see the example below :

Prior to Normalization

Item	Colors	Price	Tax
Pen	red, blue	2.0	0.20
Scale	red, yellow	2.0	0.20
Pen	red, blue	2.0	0.20
Bag	blue, black	150.00	7.80

This table is not in first normal form because :
A. There are multiple fields in color lab.
B. Records are repeating (Duplicate records) or no primary key.
First Normal Form (1NF)

Item	Colors	Price	Tax
Pen	red	2.0	0.20
Pen	blue	2.0	0.20
Scale	red	2.0	0.20
Scale	yellow	2.0	0.20
Bag	blue	150.00	7.80
Bag	black	150.00	7.80

This table is now in first normal form.

Second Normal Form (2NF)

The concept of remove the delicacy of data comes in the Second Normal Form (2NF).

A. It should meet all the requirements of the first normal form.
B. It should remove subsets of data that apply to multiple rows of a table and place them in separate tables.
C. It create relationships between these new tables and their predecessors through the use of foreign keys.
The First Normal form deals with the atomicity whereas the Second Normal Form deals with the relationship between the composite key columns and non-key columns. To achieve the next progressive level your table should satisfy the requirement of First Normal Form then move towards the Second Normal Form.

Let’s introduce a Review table as an example :

Item	Colors	Price	Tax
Pen	red	2.0	0.20
Pen	blue	2.0	0.20
Scale	red	2.0	0.20
Scale	yellow	2.0	0.20
Bag	blue	150.00	7.80
Bag	black	150.00	7.80

Table is not in Second Normal Form because the price and tax depends on the item, but not color.

Item	Colors
Pen	red
Pen	blue
Scale	red
Scale	yellow
Bag	blue
Bag	black

Item	Price	Tax
Pen	2.0	0.20
Scale	2.0	0.20
Bag	150.00	7.80

Tables are now in Second Normal Form.

Third Normal Form (3NF)

The Third Normal Form has one more additional requirement :

A. It should meet all the requirements of the second normal form.

B. It should remove columns that are not dependent upon the primary key.

In the Third Normal Form all columns depend upon the primary key. When one column depends upon the other column, table break the rule and turns into the dependency on the primary key.